using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class ExpenseClaimDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procExpenseClaim_add]";
        private string _strSPUpdateName = "dbo.[procExpenseClaim_updateRef]";
        private string _strSPDeleteName = "dbo.[procExpenseClaim_delete]";
        private string _strSPDeleteNameRef = "dbo.[procExpenseClaim_deleteRef]";
        private string _strSPGetName = "dbo.[procExpenseClaim_get]";
        private string _strSPGetAllName = "dbo.[procExpenseClaim_getall]";
        private string _strSPGetPages = "dbo.[procExpenseClaim_getpaged]";
        private string _strSPIsExist = "dbo.[procExpenseClaim_isexist]";
        private string _strSPIsExistRef = "dbo.[procExpenseClaim_isexistRef]";
        private string _strSPIsExistOurRef = "dbo.[procExpenseClaim_isexistOurRef]";
        private string _strTableName = "ExpenseClaim";
        private string _strSPGetCondName = "dbo.[procExpenseClaim_getCond]";
        private string _strSPGetSumAmt = "dbo.[procExpenseClaim_SumAmt]";
        private string _strSPGetSumOtherAmt = "dbo.[procExpenseClaim_SumOtherAmt]";
        private string _strSPUpdateField = "dbo.[procExpenseClaim_updateField]";
        #endregion Local Variable

        #region Method
        public ExpenseClaimInfo Get_ExpenseClaim(
            String ExcNo,
            String EmployeeName
        , ref string sErr)
        {
            ExpenseClaimInfo objEntr = new ExpenseClaimInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("ExcNo", ExcNo);
            AddParameter("EmployeeName", EmployeeName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = (ExpenseClaimInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            ExpenseClaimInfo result = new ExpenseClaimInfo();
            result.Business = (dt.Rows[i]["Business"] == DBNull.Value ? "" : (String)dt.Rows[i]["Business"]);
            result.Reference = (dt.Rows[i]["Reference"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["Reference"]);
            result.ExcNo = (dt.Rows[i]["ExcNo"] == DBNull.Value ? "" : (String)dt.Rows[i]["ExcNo"]);
            result.EmployeeName = (dt.Rows[i]["EmployeeName"] == DBNull.Value ? "" : (String)dt.Rows[i]["EmployeeName"]);
            result.SupportDoc = (dt.Rows[i]["SupportDoc"] == DBNull.Value ? "" : (String)dt.Rows[i]["SupportDoc"]);
            result.OurRef = (dt.Rows[i]["OurRef"] == DBNull.Value ? "" : (String)dt.Rows[i]["OurRef"]);
            result.IssueDate = (dt.Rows[i]["IssueDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["IssueDate"]);
            result.ReceiptDate = (dt.Rows[i]["ReceiptDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["ReceiptDate"]);
            result.Amt = (dt.Rows[i]["Amt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["Amt"]);
            result.OtherAmt = (dt.Rows[i]["OtherAmt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["OtherAmt"]);
            result.Currency = (dt.Rows[i]["Currency"] == DBNull.Value ? "" : (String)dt.Rows[i]["Currency"]);
            result.PymtRef = (dt.Rows[i]["PymtRef"] == DBNull.Value ? "" : (String)dt.Rows[i]["PymtRef"]);
            result.PaidDate = (dt.Rows[i]["PaidDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["PaidDate"]);
            result.PaymentTerm = (dt.Rows[i]["PaymentTerm"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["PaymentTerm"]);
            result.DueDate = (dt.Rows[i]["DueDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["DueDate"]);
            result.Description = (dt.Rows[i]["Description"] == DBNull.Value ? "" : (String)dt.Rows[i]["Description"]);
            result.DeliveryDate = (dt.Rows[i]["DeliveryDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["DeliveryDate"]);
            result.ReturnedDate = (dt.Rows[i]["ReturnedDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["ReturnedDate"]);
            result.Department = (dt.Rows[i]["Department"] == DBNull.Value ? "" : (String)dt.Rows[i]["Department"]);
            result.Emailed = (dt.Rows[i]["Emailed"] == DBNull.Value ? (Int16)0 : (Int16)dt.Rows[i]["Emailed"]);
            result.Remark1 = (dt.Rows[i]["Remark1"] == DBNull.Value ? "" : (String)dt.Rows[i]["Remark1"]);
            result.Remark2 = (dt.Rows[i]["Remark2"] == DBNull.Value ? "" : (String)dt.Rows[i]["Remark2"]);
            result.Remark3 = (dt.Rows[i]["Remark3"] == DBNull.Value ? "" : (String)dt.Rows[i]["Remark3"]);
            result.Mark = (dt.Rows[i]["Mark"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["Mark"]);
            result.TransMitRef = (dt.Rows[i]["TransMitRef"] == DBNull.Value ? "" : (String)dt.Rows[i]["TransMitRef"]);
            result.TransMitDate = (dt.Rows[i]["TransMitDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["TransMitDate"]);
            result.MarkTrm = (dt.Rows[i]["MarkTrm"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["MarkTrm"]);
            result.TransMitStat = (dt.Rows[i]["TransMitStat"] == DBNull.Value ? "" : (String)dt.Rows[i]["TransMitStat"]);
            result.Receiver = (dt.Rows[i]["Receiver"] == DBNull.Value ? "" : (String)dt.Rows[i]["Receiver"]);
            result.EmployeeCode = (dt.Rows[i]["EmployeeCode"] == DBNull.Value ? "" : (String)dt.Rows[i]["EmployeeCode"]);
            result.PaidAmt = (dt.Rows[i]["PaidAmt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["PaidAmt"]);
            result.PaidOtherAmt = (dt.Rows[i]["PaidOtherAmt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["PaidOtherAmt"]);
            result.Sent = (dt.Rows[i]["Sent"] == DBNull.Value ? (Int16)0 : (Int16)dt.Rows[i]["Sent"]);
            result.CreatedBy = (dt.Rows[i]["CreatedBy"] == DBNull.Value ? "" : (String)dt.Rows[i]["CreatedBy"]);
            result.ModifiedBy = (dt.Rows[i]["ModifiedBy"] == DBNull.Value ? "" : (String)dt.Rows[i]["ModifiedBy"]);
            result.CreatedDate = (dt.Rows[i]["CreatedDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["CreatedDate"]);
            result.ModifiedDate = (dt.Rows[i]["ModifiedDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["ModifiedDate"]);

            return result;
        }

        public DataTable GetAll_ExpenseClaim(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_ExpenseClaim(ExpenseClaimInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter("Business", objEntr.Business);
            AddParameter("Reference", objEntr.Reference);
            AddParameter("ExcNo", objEntr.ExcNo);
            AddParameter("EmployeeName", objEntr.EmployeeName);
            AddParameter("SupportDoc", objEntr.SupportDoc);
            //AddParameter("OurRef", objEntr.OurRef);
            if (objEntr.OurRef == "")
                AddParameter("OurRef", DBNull.Value);
            else
                AddParameter("OurRef", objEntr.OurRef);

            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            AddParameter("Amt", objEntr.Amt);
            AddParameter("OtherAmt", objEntr.OtherAmt);
            AddParameter("Currency", objEntr.Currency);
            AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            AddParameter("Description", objEntr.Description);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            AddParameter("Department", objEntr.Department);
            AddParameter("Emailed", objEntr.Emailed);
            AddParameter("Remark1", objEntr.Remark1);
            AddParameter("Remark2", objEntr.Remark2);
            AddParameter("Remark3", objEntr.Remark3);
            AddParameter("Mark", objEntr.Mark);
            AddParameter("TransMitRef", objEntr.TransMitRef);
            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.ModifiedDate);
            AddParameter("MarkTrm", objEntr.MarkTrm);
            AddParameter("TransMitStat", objEntr.TransMitStat);
            AddParameter("Receiver", objEntr.Receiver);
            AddParameter("EmployeeCode", objEntr.EmployeeCode);
            AddParameter("PaidAmt", objEntr.PaidAmt);
            AddParameter("PaidOtherAmt", objEntr.PaidOtherAmt);
            AddParameter("Sent", objEntr.Sent);
            AddParameter("CreatedBy", objEntr.CreatedBy);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("CreatedDate", objEntr.CreatedDate);
            //AddParameter("ModifiedDate", objEntr.ModifiedDate);
            if (objEntr.ModifiedDate == DateTime.MinValue)
                AddParameter("ModifiedDate", DBNull.Value);
            else
                AddParameter("ModifiedDate", objEntr.ModifiedDate);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_ExpenseClaim(ExpenseClaimInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("Business", objEntr.Business);
            AddParameter("Reference", objEntr.Reference);
            AddParameter("ExcNo", objEntr.ExcNo);
            AddParameter("EmployeeName", objEntr.EmployeeName);
            AddParameter("SupportDoc", objEntr.SupportDoc);
            //AddParameter("OurRef", objEntr.OurRef);
            if (objEntr.OurRef == "")
                AddParameter("OurRef", DBNull.Value);
            else
                AddParameter("OurRef", objEntr.OurRef);

            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            AddParameter("Amt", objEntr.Amt);
            AddParameter("OtherAmt", objEntr.OtherAmt);
            AddParameter("Currency", objEntr.Currency);
            AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            AddParameter("Description", objEntr.Description);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            AddParameter("Department", objEntr.Department);
            AddParameter("Emailed", objEntr.Emailed);
            AddParameter("Remark1", objEntr.Remark1);
            AddParameter("Remark2", objEntr.Remark2);
            AddParameter("Remark3", objEntr.Remark3);
            AddParameter("Mark", objEntr.Mark);
            AddParameter("TransMitRef", objEntr.TransMitRef);
            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.ModifiedDate);
            AddParameter("MarkTrm", objEntr.MarkTrm);
            AddParameter("TransMitStat", objEntr.TransMitStat);
            AddParameter("Receiver", objEntr.Receiver);
            AddParameter("EmployeeCode", objEntr.EmployeeCode);
            AddParameter("PaidAmt", objEntr.PaidAmt);
            AddParameter("PaidOtherAmt", objEntr.PaidOtherAmt);
            AddParameter("Sent", objEntr.Sent);
            AddParameter("CreatedBy", objEntr.CreatedBy);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("CreatedDate", objEntr.CreatedDate);
            //AddParameter("ModifiedDate", objEntr.ModifiedDate);
            if (objEntr.ModifiedDate == DateTime.MinValue)
                AddParameter("ModifiedDate", DBNull.Value);
            else
                AddParameter("ModifiedDate", objEntr.ModifiedDate);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Update_FieldExpenseClaim(String Reference, String FieldName, String Value)
        {

            openConnection();
            InitSPCommand(_strSPUpdateField);

            AddParameter("Reference", Reference);
            AddParameter("FieldName", FieldName);

            if (Value == "")
                AddParameter("FieldValue", DBNull.Value);
            else
                AddParameter("FieldValue", Value);
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_ExpenseClaim(
            String ExcNo,
            String EmployeeName
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("ExcNo", ExcNo);
            AddParameter("EmployeeName", EmployeeName);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Delete_ExpenseClaimRef(
            int Reference
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteNameRef);
            AddParameter("Reference", Reference);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        

        public Boolean IsExist_ExpenseClaim(
            String ExcNo,
            String EmployeeName,
            Int32 Reference
        )
        {
            InitConnect();
            InitCommand(@"SELECT     TOP (1) Reference
                FROM         ExpenseClaim
                WHERE     (ExcNo = @ExcNo) AND (EmployeeName = @EmployeeName) AND (Reference <> @Reference)",CommandType.Text);

            AddParameter("ExcNo", ExcNo);
            AddParameter("EmployeeName", EmployeeName);
            AddParameter("Reference", Reference);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public Boolean IsExist_ExpenseClaimRef(
            int Reference
        )
        {
            InitConnect();

            InitCommand(@"SELECT     TOP (1) Reference
                FROM         ExpenseClaim
                WHERE     (Reference = @Reference)", CommandType.Text);

            AddParameter("Reference", Reference);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
        public Boolean IsExist_ExpenseClaimOurRef(
            int Reference, String OurRef
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExistOurRef);

            AddParameter("Reference", Reference);
            AddParameter("OurRef", OurRef);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(ExpenseClaimInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetCond(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetSumAmt(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetSumAmt);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetSumOtherAmt(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetSumOtherAmt);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

    }
}
